import pandas as pd
import time
from DatetimeUtils import choose_day
from ListUtils import anti_join
"""
每日行情数据
"""

def deal_append(query_start_, query_end_, engine_, pro_, table_name):
    """
    通过anti_join函数处理增量数据
    """
    sql = 'select t.* from trade_cal t WHERE t.is_open = "1" and t.cal_date >= ' + query_start_ + ' and t.cal_date <= ' + query_end_
    trade_cal_days = pd.read_sql(sql, engine_)
    if len(trade_cal_days) > 0:
        # print('read_sql接口返回数据：(%s)' % trade_cal_days)
        for i in range(0, len(trade_cal_days)):
            cal_obj = trade_cal_days.loc[i]
            # print('cal_obj：(%s)' % cal_obj)
            sql = 'select f.ts_code, f.trade_date from ' + table_name +  ' f WHERE f.trade_date = ' + cal_obj.cal_date
            target_list = pd.read_sql(sql, engine_)
            source_list = pro_.daily_basic(ts_code='', trade_date=cal_obj.cal_date)
            print('每日行情数据cal_obj.cal_date：(%s)' % cal_obj.cal_date)
            if len(target_list) == 0:
                source_list.to_sql(name=table_name, con=engine_, index=False, index_label=['ts_code', 'trade_date'], if_exists='append')
                print('数据库内未找到数据则全部添加：(%s)' % source_list)
                time.sleep(2)
            else:
                source_list = anti_join(source_list, target_list, ['ts_code', 'trade_date'])
                if len(source_list) > 0:
                    print('增量：(%s)' % source_list)
                    # print(source_list)
                    # df_stock_company.drop(labels='exists',axis=1,inplace=True)
                    source_list.to_sql(name=table_name, con=engine_, index=False, index_label=['ts_code', 'trade_date'],if_exists='append')
                    time.sleep(2)



def insert_history_data_daily_basic(engine_, pro_):
    """
    查询并保存历史数据
    """
    current_year = choose_day(0).strftime('%Y')
    start_year = 2018  # 由于数据量较大，开始日期19901219
    """
    历年数据
    """
    for i in range(start_year, int(current_year) -2):
        query_start = '%d' % i + '0101'  # 查询开始
        query_end = '%d' % i + '1231'  # 查询结束
        if start_year == 1990:
            query_start = '19901219'
        if start_year == 2018:
            query_start = '20180604'
        deal_append(query_start, query_end, engine_, pro_, 'daily_basic_old')
        """
        当年数据
        """
        # query_start = choose_day(0).strftime('%Y') + '0101'
        # query_end = choose_day(0).strftime('%Y%m%d')
        # deal_append(query_start, query_end, engine_, pro_)



def update_current_data_daily_basic(engine_, pro_):
    """
    查询并更新数据，定时器使用
    从20190101开始记录
    """
    # current_year = choose_day(0).strftime('%Y')
    # start_year = 2019  # 由于数据量较大，开始日期19901219
    # for i in range(start_year, int(current_year)-1):
    #     query_start = '%d' % i + '0101'  # 查询开始
    #     query_end = '%d' % i + '1231'  # 查询结束
    #     deal_append(query_start, query_end, engine_, pro_, 'daily_basic')

    day_updates = 30  # 设定更新30天数据
    current_day = choose_day(0).strftime('%Y%m%d')
    that_day = choose_day(day_updates).strftime('%Y%m%d')
    deal_append(that_day, current_day, engine_, pro_, 'daily_basic')


